
if exists (select 1 from sysobjects where name = 'get_personpensum' and type = 'P')
begin
   drop procedure get_personpensum
   print 'Procedure: get_personpensum deleted ...'
end
go
create procedure get_personpensum(
  @prsid              int           = 3,
  @stichdatum         char(8)       = '20090831'
)
as
begin
  set nocount on
 if @stichdatum is null select @stichdatum = GETDATE()
 if @stichdatum = '00000000' select @stichdatum = GETDATE()

 select prsid = a.PrsID,
        von = a.Von ,
        bis = a.Bis,
        pensum = a.Prozent
   from Arbeitspensum a
  where a.PrsID = @prsid
    and @stichdatum >= a.Von
    and @stichdatum <= a.Bis
    and a.Bis = (select MAX(ap.Bis) from Arbeitspensum ap 
                                 where ap.PrsID = a.PrsID
                                   and @stichdatum >= ap.Von
                                   and @stichdatum <= ap.Bis)

end
go
print 'Procedure: get_personpensum done ...'
go
grant exec on get_personpensum to prsadmins with grant option
go
grant exec on get_personpensum to prsusers
go

